﻿/*----------------------------------------------------
 * Author : Tharinduni Udugamasuriya
 * FinCon dll for Handeling DB 
 * Reader : Handeling DB Select Commands - inner
 ---------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Windows.Forms;

namespace DSPL.DataAccess
{
    public class Reader : QueryBuilder
    {
        private string CommandText(SpCaller spCaller)
        {
            try
            {
                StringBuilder sb = new StringBuilder("Select ");
                sb.Append(spCaller.FcnName);
                sb.Append("('");
                foreach (ParameterWithValue item in spCaller.SpParamValues)
                {
                    sb.Append(item.SpParameter.Value);
                    sb.Append("','");
                }
                sb.Remove(sb.Length - 2, 2);
                sb.Append(")");
                return sb.ToString();
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public object ScalerDB(SpCaller spCaller, ref  SqlConnection SqlConn)
        {
            try
            {
                if (SqlConn.State == System.Data.ConnectionState.Open)
                {

                    SqlCommand SqlCmd = new SqlCommand(spCaller.SpName, SqlConn);
                    SqlCmd.CommandType = CommandType.StoredProcedure;

                    foreach (ParameterWithValue item in spCaller.SpParamValues)
                    {
                        SqlCmd.Parameters.Add(item.SpParameter);
                        item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                    }

                    return SqlCmd.ExecuteScalar();

                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public object ScalerDB(Query query, ref  SqlConnection SqlConn)
        {
            try
            {
                if (SqlConn.State == System.Data.ConnectionState.Open)
                {
                    SqlCommand SqlCmd = new SqlCommand(SelectQueryBuilder(query), SqlConn);
                    SqlCmd.CommandType = CommandType.Text;
                    return SqlCmd.ExecuteScalar();

                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public DataTable SelectDB(SpCaller spCaller, bool timeOutOff, ref SqlConnection SqlConn)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                if (!timeOutOff)
                {
                    return SelectDB(spCaller, ref SqlConn);
                }
                else
                {
                    da.SelectCommand = new SqlCommand(spCaller.SpName, SqlConn);
                    SpCaller spCall = spCaller;

                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.CommandTimeout = 0;
                    foreach (ParameterWithValue item in spCaller.SpParamValues)
                    {
                        da.SelectCommand.Parameters.Add(item.SpParameter);
                        item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                    }

                    if (SqlConn.State == System.Data.ConnectionState.Open)
                    {
                        da.Fill(dt);
                        return dt;
                    }
                    else
                    {
                        throw new Exception("Database Connection is not open");
                    }
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (dt != null)
                {
                    dt = null;
                }
                if (da != null)
                {
                    da = null;
                }
            }
        }

        public DataTable SelectDB(Query query, bool timeOutOff, ref SqlConnection SqlConn,bool isParameterized = false)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                if (!timeOutOff)
                {
                    return SelectDB(query, ref SqlConn);
                }
                else
                {
                    SqlCommand SqlCmd = new SqlCommand();
                    
                    if (isParameterized)
                        SqlCmd = (SqlCommand)SelectQueryBuilder(query,ref SqlCmd);
                    else
                        SqlCmd.CommandText = SelectQueryBuilder(query);

                    if (SqlCmd != null && SqlCmd.CommandText != "")
                    {
                        SqlCmd.Connection = SqlConn;
                        SqlCmd.CommandType = CommandType.Text;
                        SqlCmd.CommandTimeout = 0;

                        da = new SqlDataAdapter(SqlCmd);
                    }                    
                    
                    if (SqlConn.State == System.Data.ConnectionState.Open)
                    {
                        da.Fill(dt);
                        return dt;
                    }
                    else
                    {
                        throw new Exception("Database Connection is not open");
                    }
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (dt != null)
                {
                    dt = null;
                }
                if (da != null)
                {
                    da = null;
                }
            }
        }

        public DataTable SelectDB(SpCaller spCaller, ref SqlConnection SqlConn)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                da.SelectCommand = new SqlCommand(spCaller.SpName, SqlConn);
                SpCaller spCall = spCaller;

                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                foreach (ParameterWithValue item in spCaller.SpParamValues)
                {
                    da.SelectCommand.Parameters.Add(item.SpParameter);
                    item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                }

                if (SqlConn.State == System.Data.ConnectionState.Open)
                {
                    da.Fill(dt);
                    return dt;
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (dt != null)
                {
                    dt = null;
                }
                if (da != null)
                {
                    da = null;
                }
            }
        }

        public DataTable SelectDB(Query query, ref SqlConnection SqlConn, bool isParameterized = false)
        {
            //DataTable dt = new DataTable();
            //SqlDataReader resultReader = null;
            //try
            //{
            //    SqlCommand SqlCmd = new SqlCommand();

            //    if (isParameterized)
            //        SqlCmd = (SqlCommand)SelectQueryBuilder(query, ref SqlCmd);
            //    else
            //        SqlCmd.CommandText = SelectQueryBuilder(query);

            //    if (SqlCmd != null && SqlCmd.CommandText != "")
            //    {
            //        SqlCmd.Connection = SqlConn;
            //        SqlCmd.CommandType = CommandType.Text;

            //        resultReader = SqlCmd.ExecuteReader();
            //    }

            //    dt.Load(resultReader);
            //    return dt;
            //}
            //catch (SqlException ex)
            //{

            //    throw ex;
            //}
            //catch (Exception ex)
            //{

            //    throw ex;
            //}
            //finally
            //{
            //    if (dt != null)
            //    {
            //        dt = null;
            //    }
            //    //if (da != null)
            //    //{
            //    //    da = null;
            //    //}
            //}
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                SqlCommand SqlCmd = new SqlCommand();

                if (isParameterized)
                    SqlCmd = (SqlCommand)SelectQueryBuilder(query, ref SqlCmd);
                else
                    SqlCmd.CommandText = SelectQueryBuilder(query);

                if (SqlCmd != null && SqlCmd.CommandText != "")
                {
                    SqlCmd.Connection = SqlConn;
                    SqlCmd.CommandType = CommandType.Text;

                    da = new SqlDataAdapter(SqlCmd);
                }

                if (SqlConn.State == System.Data.ConnectionState.Open)
                {
                    da.Fill(dt);
                    return dt;
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (dt != null)
                {
                    dt = null;
                }
                if (da != null)
                {
                    da = null;
                }
            }
        }

        public object FunctionDB(SpCaller spCaller, ref SqlConnection SqlConn)
        {
            try
            {
                if (SqlConn.State == System.Data.ConnectionState.Open)
                {

                    SqlCommand SqlCmd = new SqlCommand(CommandText(spCaller), SqlConn);
                    SqlCmd.CommandType = CommandType.Text;

                    return SqlCmd.ExecuteScalar();
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public DataTable SelectDB(SpCaller spCaller, ref SqlTransaction sqlTran, ref SqlConnection SqlConn)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = SqlConn.BeginTransaction();
                }

                da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(spCaller.SpName, SqlConn);
                SpCaller spCall = spCaller;

                da.SelectCommand.Transaction = sqlTran;
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.CommandTimeout = 0;
                foreach (ParameterWithValue item in spCaller.SpParamValues)
                {
                    da.SelectCommand.Parameters.Add(item.SpParameter);
                    item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                }

                if (SqlConn.State == System.Data.ConnectionState.Open)
                {
                    da.Fill(dt);
                    if (dt.Rows.Count <= 0)
                    {
                        throw new Exception("Invalid data selection:" + Environment.NewLine + "Data table can not find");
                    }
                    return dt;
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }

            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (dt != null)
                {
                    dt = null;
                }
                if (da != null)
                {
                    da = null;
                }
            }
        }

        public DataTable SelectDB(Query query, ref SqlTransaction sqlTran, ref SqlConnection SqlConn, bool isParameterized = false)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = SqlConn.BeginTransaction();
                }

                SqlCommand SqlCmd = new SqlCommand();

                if (isParameterized)
                    SqlCmd = (SqlCommand)SelectQueryBuilder(query, ref SqlCmd);
                else
                    SqlCmd.CommandText = SelectQueryBuilder(query);

                if (SqlCmd != null && SqlCmd.CommandText != "")
                {
                    SqlCmd.Connection = SqlConn;
                    SqlCmd.CommandType = CommandType.Text;
                    SqlCmd.Transaction = sqlTran;
                    SqlCmd.CommandTimeout = 0;

                    da = new SqlDataAdapter(SqlCmd);
                }

                if (SqlConn.State == System.Data.ConnectionState.Open)
                {
                    da.Fill(dt);
                    return dt;
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }

            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (dt != null)
                {
                    dt = null;
                }
                if (da != null)
                {
                    da = null;
                }
            }
        }

    }
}
